﻿using ClosedXML.Excel;
using QMERP.Infrastructure.CustomException;
using QMERP.Infrastructure.Extensions;
using System.ComponentModel;
using System.Data;
using System.Reflection;
using System.Text;

namespace QMERP.Infrastructure.Tools
{
    public static class ExcelHelper
    {
        /// <summary>
        /// ExcelStream转DataTable
        /// </summary>
        /// <param name="stream"></param>
        /// <returns></returns>
        public static DataTable ExcelStreamToDataTable(Stream stream)
        {
            var workbook = stream.ToXLWorkbook();
            var sheet = workbook.Worksheet(1);
            int cellCount = sheet.FirstRowUsed().LastCellUsed().Address.ColumnNumber;
            DataTable data = new();
            for (int i = 1; i <= cellCount; i++)
            {
                var cell = sheet.Cell(1, i);
                if (cell != null)
                {
                    string cellValue = sheet.Cell(1, i).GetString();
                    if (cellValue != null)
                    {
                        DataColumn column = new(cellValue);
                        data.Columns.Add(column);
                    }
                }
            }
            //
            int startRow = sheet.FirstRowUsed().RowNumber() + 1;
            //最后一列的标号
            int rowCount = sheet.LastRowUsed().RowNumber();
            for (int i = startRow; i <= rowCount; ++i)
            {
                var row = sheet.Row(i);
                if (row == null) continue; //没有数据的行默认是null　　　　　　　

                DataRow dataRow = data.NewRow();
                for (int j = 1; j <= cellCount; ++j)
                {
                    if (row.Cell(j) != null) //同理，没有数据的单元格都默认是null
                        dataRow[j-1] = row.Cell(j).Value;
                }
                data.Rows.Add(dataRow);
            }
            return data;
        }

        /// <summary>
        /// 判断是不是XYTable的格式,不是的话直接报异常
        /// </summary>
        /// <param name="sheet"></param>
        /// <exception cref="ApiException"></exception>
        public static void IsXYTable(IXLWorksheet sheet)
        {
            if (sheet.Row(1) == null || sheet.Row(1).Cell(1) == null || sheet.Row(1).Cell(3) == null || sheet.Row(2).Cell(1) == null)
            {
                throw new ApiException("导入失败，Excel不是指定格式！");
            }
            if (sheet.Row(2) == null || sheet.Row(2).Cell(1).GetString().Contains("SPH") == false)
            {
                throw new ApiException("导入失败，Excel不是指定格式！");
            }
        }
       
        /// <summary>
        /// XYStream表转LensDetails
        /// </summary>
        /// <param name="warehouseCode"></param>
        /// <param name="lensCode"></param>
        /// <param name="stream"></param>
        /// <returns></returns>
        public static DataTable XYExcelStreamToDataTable(Stream stream)
        {
            //IWorkbook workbook = WorkbookFactory.Create(stream);
            DataTable dt = new DataTable();
            //for (int num = 0; num < workbook.NumberOfSheets; num++)
            //{
            //    ISheet sheet = workbook.GetSheetAt(num);

            //    IsXYTable(sheet);

            //    IRow headerRow = sheet.GetRow(1);//第2行为标题行  

            //    int cellCount = headerRow.LastCellNum;
            //    int rowCount = sheet.LastRowNum;
                
            //    for (int i = 0; i < cellCount; i++)
            //    {
            //        string title = GetCellValue(headerRow.GetCell(i)).Replace("C", "");
            //        if (title != "000" && title != "Total" && title != "SPH")
            //            title = "-" + title;
            //        dt.Columns.Add(title);
            //    }
            //    for (int j = 2; j <= rowCount; j++)
            //    {
            //        DataRow dr = dt.NewRow();
            //        for (int i = 0; i < cellCount; i++)
            //        {
            //            IRow row = sheet.GetRow(j);
            //            string v = GetCellValue(row.GetCell(i)).Replace("S", "").Replace("+", "");
            //            if (v == "-000")
            //                v = v.Replace("-", "");
            //            dr[i] = v;
            //        }
            //        dt.Rows.Add(dr);
            //    }
            //}
            return dt;
        }
        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <returns></returns>
        public static MemoryStream ToExcel<T>(List<T> list)where T : class
        {
            var workbook = new XLWorkbook();
            var sheet = workbook.AddWorksheet();
            var headerRow = sheet.Row(1);
            Type objType = typeof(T);
            var propInfos = objType.GetProperties();
            //取属性上的自定义特性
            int c = 0;
            foreach (PropertyInfo propInfo in propInfos)
            {
                object[] objAttrs = propInfo.GetCustomAttributes(typeof(DescriptionAttribute), true);
                if (objAttrs.Length > 0)
                {
                    DescriptionAttribute? attr = objAttrs[0] as DescriptionAttribute;
                    if (attr != null)
                    {
                        headerRow.Cell(c).SetValue(attr.Description);
                        c++;
                    }
                }
            }
            for (int i = 0; i < list.Count; i++)
            {
                var item = list[i];
                var row = sheet.Row(i + 1);
                int j = 0;
                foreach (PropertyInfo propInfo in propInfos)
                {
                    object[] objAttrs = propInfo.GetCustomAttributes(typeof(DescriptionAttribute), true);
                    if (objAttrs.Length > 0)
                    {
                        DescriptionAttribute? attr = objAttrs[0] as DescriptionAttribute;
                        if (attr != null)
                        {
                            object? value = propInfo.GetValue(list[i], null);
                            string obj = value == null ? "" : value.ToString()!;
                            var cell = row.Cell(j);
                            if (obj == "")
                            {
                                cell.SetValue(obj);
                            }
                            else
                            {
                                string? type = propInfo.PropertyType.FullName;
                                if (type == null || type.Contains("System.String"))
                                {
                                    cell.SetValue(obj);
                                }
                                else if (type.Contains("System.DateTime"))
                                {
                                    DateTime.TryParse(obj, out DateTime dateV);
                                    cell.SetValue(dateV.ToString("yyyy/MM/dd HH:mm:ss"));
                                }
                                else if (type.Contains("System.Boolean"))
                                {
                                    bool.TryParse(obj, out bool boolV);
                                    cell.SetValue(boolV);
                                }
                                else if (type.Contains("System.Int") || type.Contains("System.Byte"))
                                {
                                    int.TryParse(obj, out int intV);
                                    cell.SetValue(intV);
                                }
                                else if (type.Contains("System.Decimal") || type.Contains("System.Double"))
                                {
                                    double.TryParse(obj, out double doubV);
                                    cell.SetValue(doubV);
                                }
                                else
                                {
                                    cell.SetValue("");
                                }
                            }
                            j++;
                        }
                    }
                }
            }
            for (int columnNum = 1; columnNum <= headerRow.LastCellUsed().Address.ColumnNumber; columnNum++)
            {
                double columnWidth = sheet.Column(columnNum).Width / 256;
                for (int rowNum = 1; rowNum <= sheet.LastRowUsed().RowNumber(); rowNum++)
                {
                    IXLRow currentRow;
                    //当前行未被使用过  
                    if (sheet.Row(rowNum) == null)
                    {
                        currentRow = sheet.Row(rowNum);
                    }
                    else
                    {
                        currentRow = sheet.Row(rowNum);
                    }

                    if (currentRow.Cell(columnNum) != null)
                    {
                        var currentCell = currentRow.Cell(columnNum);
                        int length = Encoding.Default.GetBytes(currentCell.ToString() ?? "").Length;
                        if (columnWidth < length)
                        {
                            columnWidth = length;
                        }
                    }
                }
                sheet.Column(columnNum).Width = (columnWidth + 5) * 256;
            }
            MemoryStream ms = new();

            workbook.SaveAs(ms);
            workbook.Dispose();
            ms.Flush();
            ms.Position = 0;
            return ms;
        }
    }
}
